package com.jsmtr.rms.service.impl;

import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import com.jsmtr.rms.entity.*;
import org.springframework.stereotype.Service;

import com.jsmtr.rms.dao.DaoService;
import com.jsmtr.rms.entity.vo.JobList;
import com.jsmtr.rms.service.INZService;
import com.jsmtr.rms.utils.BeanTools;
import com.jsmtr.rms.utils.EnumTools;
import com.jsmtr.rms.utils.StringTools;
import com.jsmtr.rms.utils.db.DbUtil;
import org.springframework.transaction.annotation.Transactional;

@Service
public class NZService implements INZService {

    @Resource
    private DaoService dao;

    public void setDao(DaoService dao) {
        this.dao = dao;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<JobList> selectJobs(String key) throws Exception {
        StringBuffer hql = new StringBuffer("select new com.jsmtr.rms.entity.vo.JobList(job.id,demand.jobname,demand.deptname,pro.startdate,pro.enddate) from ProjectInfo pro,ProjectInfoJob job,PlanPersondemand demand ");

        hql.append("where demand.id=job.did and job.pid=pro.id ");
       // hql.append("and pro.recruittype = '" + EnumTools.RecruitType.内部招聘.ordinal() + "' ");
        hql.append("and pro.state != '" + EnumTools.PlanState.编辑中.ordinal() + "' ");
        hql.append("and pro.enddate >=:today ");
        hql.append("and pro.startdate <=:today ");
        hql.append("and demand.jobname like '%" + key + "%'");

        return dao.find(hql.toString(), "today", new Date());
    }

    @Override
    public ProjectInfo getProjectByJid(String jid) throws Exception {
        return (ProjectInfo) dao.findUnique("from ProjectInfo where id=(select pid from ProjectInfoJob where id='" + jid + "')");
    }

    @Override
    public NZResume getResume(String code) throws Exception {
        return (NZResume) dao.findUnique("from NZResume where code='" + code + "'");
    }

    @Override
    public NZResume getResumeByID(String id) throws Exception {
        return (NZResume) dao.findUnique("from NZResume where id='" + id + "'");
    }

    @Override
    public NZResume getResumeByCardno(String cardno) throws Exception {
        return (NZResume) dao.findUnique("from NZResume where cardno='" + cardno + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<NZResumeEducation> getEducations(String code) throws Exception {
        return dao.find("from NZResumeEducation where code='" + code + "' ");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<NZResumeWork> getWorks(String code) throws Exception {
        return dao.find("from NZResumeWork where code='" + code + "' order by startdate");
    }

    @Override
    public NZResumeWork getwork(String id) throws Exception {
        return (NZResumeWork) dao.findUnique("from NZResumeWork where id='" + id + "'");
    }

    @Override
    public DNZResume getDResume(String code, String pid) throws Exception {
        return (DNZResume) dao.findUnique("from DNZResume where code='" + code + "' and pid='" + pid + "'");
    }

    @Override
    public DNZResume getDResume(String id) throws Exception {
        return (DNZResume) dao.findUnique("from DNZResume where id='" + id + "'");
    }

    @Override
    public List<DNZResume> getDResumes(String[] ids) throws Exception {
        return dao.find("from DNZResume where id in(:idlist)", ids);
    }

    @Override
    public DUserResume getDuserResume(String id) throws Exception {
        return (DUserResume) dao.findUnique("from DUserResume where id='" + id + "'");
    }


    @SuppressWarnings("unchecked")
    @Override
    public List<DNZResumeEducation> getEducationByRid(String rid) throws Exception {
        return dao.find("from DNZResumeEducation where rid='" + rid + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DNZResumeWork> getWorkByRid(String rid) throws Exception {
        return dao.find("from DNZResumeWork where rid='" + rid + "'");
    }

    @Override
    public ProjectInfoFlow getFlow(String pid, int forder) throws Exception {
        return (ProjectInfoFlow) dao.findUnique("from ProjectInfoFlow where pid='" + pid + "' and forder='" + forder + "'");
    }

    @Override
    public Map<String, Object> getUsers(int page, int perpage, DUserResume user) throws Exception {
        StringBuffer queryHQL = new StringBuffer("from DUserResume p ");
        StringBuffer countHQL = new StringBuffer("select count(*) from DUserResume p ");
        StringBuffer condition = new StringBuffer("where 1=1 ");

        condition.append(BeanTools.hqlWhere(user, "p"));

        queryHQL.append(condition);
        countHQL.append(condition);

        return DbUtil.pageQuery(page, perpage, queryHQL.toString(), countHQL.toString(), dao);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DNZResume> getUsers(String pid, String[] ids) throws Exception {
        return dao.find("from DNZResume where pid='" + pid + "' and id in(:idlist)", ids);
    }

    @Override
    public ProjectInfoFlow getNextFlow(String pid, String deptcode, String fid) throws Exception {
        StringBuilder hql = new StringBuilder("from ProjectInfoFlow where pid='" + pid + "' and deptcode='" + deptcode + "' ");
        hql.append("and forder>(select forder from ProjectInfoFlow where id='" + fid + "') order by forder");
        return (ProjectInfoFlow) dao.findFirst(hql.toString());
    }

    @Override
    public ProjectInfoFlow getFlow(String id) throws Exception {
        return (ProjectInfoFlow) dao.findUnique("from ProjectInfoFlow where id='" + id + "'");
    }

    @Override
    public Map<String, Object> getMSUsers(int page, int perpage, DNZResume user, String fid, String msresult, int notice) throws Exception {
        StringBuffer queryHQL = new StringBuffer("select r.id,s.id as userid,r.code,r.name,r.sex,r.age,r.zzmm,r.groupdate,r.education,r.tel,s.starttime,s.address,s.notice,s.point,s.result,r.fid,r.fqstate ");
        StringBuffer countHQL = new StringBuffer("select count(1) ");
        StringBuffer condition = new StringBuffer("from (select * from DoMS_User u where u.fid = '" + fid + "' ");

        if (StringTools.trimNotEmpty(msresult)) {
            condition.append(" and u.result='" + msresult + "' ");
        }
        if (notice != -1) {
            condition.append(" and u.notice='" + notice + "' ");
        }
        condition.append(") s,DNZResume r where r.id=s.userid(+)");

        condition.append(BeanTools.hqlWhere(user, "r"));
        queryHQL.append(condition);
        countHQL.append(condition);

        System.out.println(queryHQL);

        return DbUtil.pageSqlQuery(page, perpage, queryHQL.toString(), countHQL.toString(), dao);
    }

    @Override
    public Map<String, Object> getBSUsers(int page, int perpage, DNZResume user, String fid, String result, int notice) throws Exception {
        StringBuffer queryHQL = new StringBuffer("select r.id,u.id as userid,u.ticketnum,r.code,r.name,r.education,r.tel,u.kcname,u.zhszpoint,u.zhszresult,u.gxcppoint,u.gxcpresult,u.zycppoint,u.zycpresult,u.point,u.result,u.notice,r.fid,u.seatnum ");
        StringBuffer countHQL = new StringBuffer("select count(1) ");
        StringBuffer condition = new StringBuffer("from DNZResume r,(select * from DoBS_User where fid='" + fid + "') u where r.id=u.userid(+) ");

        condition.append(BeanTools.hqlWhere(user, "r"));

        if (StringTools.trimNotEmpty(result)) {
            condition.append(" and u.result like '%" + result + "%' ");
        }
        if (notice != -1) {
            condition.append(" and u.notice='" + notice + "' ");
        }

        queryHQL.append(condition);
        countHQL.append(condition);

        System.out.println(queryHQL);

        return DbUtil.pageSqlQuery(page, perpage, queryHQL.toString(), countHQL.toString(), dao);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Object[]> getBdUserCount(String pid) throws Exception {
        return dao.executeSQLQuery("select count(*),t.jid from DNZResume t where t.pid='" + pid + "' and t.bdstate='已报到' group by t.jid");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DNZResume> getUsers(String code) throws Exception {
        return dao.find("from DNZResume where code='" + code + "' order by addDate desc");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ProjectInfoFlow> getProjectInfoFlows(String pid) throws Exception {
        return dao.find("from ProjectInfoFlow where pid='" + pid + "' order by forder");
    }

    @Override
    public DoMSUser getMsUser(String fid, String userid) throws Exception {
        return (DoMSUser) dao.findUnique("from DoMSUser where fid='" + fid + "' and userid='" + userid + "'");
    }

    @Override
    public DoBSUser getBsUser(String fid, String userid) throws Exception {
        return (DoBSUser) dao.findUnique("from DoBSUser where fid='" + fid + "' and userid='" + userid + "'");
    }


}
